package com.tsd.core.excel.export;

import com.tsd.core.config.Config;
import com.tsd.core.constants.ConfigKeyConstants;
import com.tsd.core.constants.StaticParameters;
import com.tsd.core.utils.HlpUtils;
import com.tsd.core.vo.HlpException;
import com.tsd.core.excel.impexcel.ExcelFileUtil;
import com.tsd.core.utils.StringUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.*;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class ExcelExportFromTemplate {

    public static final int MAX_ROW = 10000;
    private String templateExcelFile;
    private List<List<String>> readExcelData = null;
    private String ext;//文件后缀
    private Workbook workbook;
    private final List<Sheet> sheets = new ArrayList<Sheet>(1);
    private Sheet sheet;
    private Row row;
    private CellStyles cellStyles;
    private int rowIndex = 1;//下一个输出数据的行号
    private final List<Method> methodList = new ArrayList<Method>();

    public ExcelExportFromTemplate() {
    }

    public ExcelExportFromTemplate(String file, Class clazz) throws Exception {
        this.templateExcelFile = file;
        readTemplate();
        for (String s : readExcelData.get(1)) {
            if (!HlpUtils.isEmpty(s)) {
                Method addMethod = clazz.getMethod("get" + StringUtil.firstUpperCase(s));
                methodList.add(addMethod);
            } else {
                methodList.add(null);
            }
        }
        InputStream inputStream = new FileInputStream(this.templateExcelFile);
        int end = this.templateExcelFile.lastIndexOf(".");
        this.ext = templateExcelFile.substring(end + 1);
        if (this.ext.equalsIgnoreCase(ExcelType.XLS.getType())) {
            POIFSFileSystem system = new POIFSFileSystem(inputStream);
            this.workbook = new HSSFWorkbook(system);
        } else if (this.ext.equalsIgnoreCase(ExcelType.XLSX.getType())) {
            this.workbook = new XSSFWorkbook(inputStream);
        }
        this.sheet = workbook.getSheetAt(0);
        this.sheets.add(this.sheet);
    }

    public ExcelExportFromTemplate(String file, int fieldIndex, Class clazz) throws Exception {
        this.templateExcelFile = file;
        readTemplate();
        for (String s : readExcelData.get(fieldIndex)) {
            if (!HlpUtils.isEmpty(s)) {
                Method addMethod = clazz.getMethod("get" + StringUtil.firstUpperCase(s));
                methodList.add(addMethod);
            } else {
                methodList.add(null);
            }
        }
        InputStream inputStream = new FileInputStream(this.templateExcelFile);
        int end = this.templateExcelFile.lastIndexOf(".");
        this.ext = templateExcelFile.substring(end + 1);
        if (this.ext.equalsIgnoreCase(ExcelType.XLS.getType())) {
            POIFSFileSystem system = new POIFSFileSystem(inputStream);
            this.workbook = new HSSFWorkbook(system);
        } else if (this.ext.equalsIgnoreCase(ExcelType.XLSX.getType())) {
            this.workbook = new XSSFWorkbook(inputStream);
        }
        this.sheet = workbook.getSheetAt(0);
        this.sheets.add(this.sheet);
        this.rowIndex = fieldIndex;
    }

    private String getFileName(String type) throws Exception {
        // 上传的根目录
        String rootPath = Config.getParameterValue(ConfigKeyConstants.UPLOAD_ROOT_PATH);
        String fileName = HlpUtils.date2String(new Date(), HlpUtils.Format_Yyyy_MM_dd_HH_mm_ss_FileName) + Math.random();
        String excelName = rootPath + "/" + StaticParameters.EXPORT_PATH + "/" + fileName + "." + type;
        File file = new File(excelName);
        if (!file.getParentFile().exists()) {
            file.getParentFile().mkdirs();
        }
        if (!file.exists()) {
            file.createNewFile();
        }
        return StaticParameters.EXPORT_PATH + "/" + fileName + "." + type;
    }

    public String saveDestFile() throws Exception {
        String fileName = getFileName(ext);
        // 上传的根目录
        String rootPath = Config.getParameterValue(ConfigKeyConstants.UPLOAD_ROOT_PATH);
        export(rootPath + "/" + fileName);
        return fileName;
    }

    private void export(String excelFileName) throws Exception {
        try {
            FileOutputStream outputStream = new FileOutputStream(excelFileName);
            export(outputStream);
        } catch (FileNotFoundException e) {
            throw new RuntimeException("导出Excel文件出错！", e);
        }
    }

    private void export(FileOutputStream outputStream) throws RuntimeException {
        try {
            workbook.setForceFormulaRecalculation(true);
            workbook.write(outputStream);
            outputStream.flush();
        } catch (FileNotFoundException e) {
            throw new RuntimeException("导出Excel文件出错！", e);
        } catch (IOException e) {
            throw new RuntimeException("写入Excel文件出错！", e);
        } finally {
            try {
                if (outputStream != null) {
                    outputStream.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

    private void setCell(int index, String value, CellStyle style) {
        Cell cell = this.row.createCell(index);
        cell.setCellValue(value);
        if (style != null) {
            cell.setCellStyle(style);
        }
    }

    private void setCell(int index, String value) {
        if (cellStyles != null) {
            setCell(index, value, cellStyles.getStringStyle());
        } else {
            setCell(index, value, null);
        }
    }

    private void setCell(int index, Date value, CellStyle style) {
        Cell cell = this.row.createCell(index);
        cell.setCellValue(value);
        if (style != null) {
            cell.setCellStyle(style);
        }
    }

    private void setCell(int index, Date value) {
        if (cellStyles != null) {
            setCell(index, value, cellStyles.getStringStyle());
        } else {
            setCell(index, value, null);
        }
    }

    private void setCell(int index, int value, CellStyle style) {
        Cell cell = this.row.createCell(index);
        cell.setCellValue(value);
        if (style != null) {
            cell.setCellStyle(style);
        }
    }

    private void setCell(int index, int value) {
        if (cellStyles != null) {
            setCell(index, value, cellStyles.getStringStyle());
        } else {
            setCell(index, value, null);
        }
    }

    private void setCell(int index, Integer value, CellStyle style) {
        Cell cell = this.row.createCell(index);
        cell.setCellValue(value);
        if (style != null) {
            cell.setCellStyle(style);
        }
    }

    private void setCell(int index, Integer value) {
        if (cellStyles != null) {
            setCell(index, value, cellStyles.getStringStyle());
        } else {
            setCell(index, value, null);
        }
    }

    private void setCell(int index, double value, CellStyle style) {
        Cell cell = this.row.createCell(index);
        cell.setCellValue(value);
        if (style != null) {
            cell.setCellStyle(style);
        }
    }

    private void setCell(int index, double value) {
        if (cellStyles != null) {
            setCell(index, value, cellStyles.getStringStyle());
        } else {
            setCell(index, value, null);
        }
    }

    private void setCell(int index, Double value, CellStyle style) {
        Cell cell = this.row.createCell(index);
        cell.setCellValue(value);
        if (style != null) {
            cell.setCellStyle(style);
        }
    }

    private void setCell(int index, Double value) {
        if (cellStyles != null) {
            setCell(index, value, cellStyles.getStringStyle());
        } else {
            setCell(index, value, null);
        }
    }

    private void setCell(int index, float value, CellStyle style) {
        Cell cell = this.row.createCell(index);
        cell.setCellValue(value);
        if (style != null) {
            cell.setCellStyle(style);
        }
    }

    private void setCell(int index, float value) {
        if (cellStyles != null) {
            setCell(index, value, cellStyles.getStringStyle());
        } else {
            setCell(index, value, null);
        }
    }

    private void setCell(int index, Float value, CellStyle style) {
        Cell cell = this.row.createCell(index);
        cell.setCellValue(value);
        if (style != null) {
            cell.setCellStyle(style);
        }
    }

    private void setCell(int index, Float value) {
        if (cellStyles != null) {
            setCell(index, value, cellStyles.getStringStyle());
        } else {
            setCell(index, value, null);
        }
    }

    private void setCell(int index, Long value, CellStyle style) {
        Cell cell = this.row.createCell(index);
        cell.setCellValue(value);
        if (style != null) {
            cell.setCellStyle(style);
        }
    }

    private void setCell(int index, Long value) {
        if (cellStyles != null) {
            setCell(index, value, cellStyles.getStringStyle());
        } else {
            setCell(index, value, null);
        }
    }

    private void setCell(int index, BigDecimal value, CellStyle style) {
        Cell cell = this.row.createCell(index);
        cell.setCellValue(value.doubleValue());
        if (style != null) {
            cell.setCellStyle(style);
        }
    }

    private void setCell(int index, BigDecimal value) {
        if (cellStyles != null) {
            setCell(index, value, cellStyles.getStringStyle());
        } else {
            setCell(index, value, null);
        }
    }

    private void setCellHeader(int index, String value, CellStyle style) {
        Cell cell = this.row.createCell(index);
        cell.setCellValue(value);
        if (style != null) {
            cell.setCellStyle(style);
        }
    }

    private void setCellHeader(int index, String value) {
        if (cellStyles != null) {
            setCellHeader(index, value, cellStyles.getStringStyle());
        } else {
            setCellHeader(index, value, null);
        }
    }

    private void setCellFormula(int index, String foemula, CellStyle style) {
        Cell cell = this.row.createCell(index);
        cell.setCellFormula(foemula);
        cell.setCellValue("");
        if (style != null) {
            cell.setCellStyle(style);
        }
    }

    private void setCellFormula(int index, String foemula) {
        if (cellStyles != null) {
            setCellFormula(index, foemula, cellStyles.getStringStyle());
        } else {
            setCellFormula(index, foemula, null);
        }
    }

    private void createRow() {
        if (getRowIndex() > MAX_ROW) {
            this.createSheet();
        }
        this.row = this.sheet.createRow(rowIndex++);
    }

    private void createSheet() {
        this.sheet = this.workbook.createSheet();
        this.sheets.add(this.sheet);
        rowIndex = 0;
    }

    private void createRow(List<String> headers) {
        if (getRowIndex() > MAX_ROW) {
            this.createSheet();
        }
        this.row = this.sheet.createRow(rowIndex++);
        if (rowIndex == 1) {
            for (int i = 0; i < headers.size(); i++) {
                this.setCellHeader(i, headers.get(i));
            }
        }
    }

    private String toRadix(Integer num) {
        String[] array = {"A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z"};
        int count = array.length;
        StringBuilder builder = null;
        if (num / count != 0) {
            builder = new StringBuilder(array[num / (count - 1)]);
            if (num % count == 0) {
                builder.append(array[num % count]);
            } else {
                builder.append(array[num % (count - 1)]);
            }
        } else {
            builder = new StringBuilder(array[num - 1]);
        }
        return builder.toString();
    }

    private Workbook getWorkbook() {
        return workbook;
    }

    private Sheet getSheet() {
        return sheet;
    }

    private Row getRow() {
        return row;
    }

    private int getRowIndex() {
        return rowIndex;
    }

    private CellStyles getCellStyles() {
        return cellStyles;
    }

    private void setCellStyles(CellStyles cellStyles) {
        this.cellStyles = cellStyles;
    }

    private List<Sheet> getSheets() {
        return sheets;
    }

    private List<List<String>> readTemplate() throws Exception {
        ExcelFileUtil<Object> efu = new ExcelFileUtil<Object>();
        try {
            readExcelData = efu.readExcelData(templateExcelFile, 0);
            if (readExcelData != null && readExcelData.size() > 1) {
                int count1 = readExcelData.get(0).size();
                int count2 = readExcelData.get(1).size();
            } else {
                throw new HlpException("导出模板应该有2行数据");
            }
        } catch (Exception e) {
            e.printStackTrace();
            throw new HlpException("读取模板出错：" + e.getMessage());
        }
        return readExcelData;
    }

    public void writeRow(Object o) throws Exception {
        this.row = this.sheet.createRow(this.rowIndex);
        this.row.setHeightInPoints(20f);
        List<String> fields = readExcelData.get(1);
        Drawing drawing = this.sheet.createDrawingPatriarch();
        for (int i = 0; i < fields.size(); i++) {
            Method method = methodList.get(i);
            if (method != null) {
                Object obj = method.invoke(o);
                if (obj != null) {
                    if (obj instanceof String) {
                        this.setCell(i, (String) obj);
                    } else if (obj instanceof Integer) {
                        this.setCell(i, (Integer) obj);
                    } else if (obj instanceof Double) {
                        this.setCell(i, (Double) obj);
                    } else if (obj instanceof Long) {
                        this.setCell(i, (Long) obj);
                    } else if (obj instanceof BigDecimal) {
                        this.setCell(i, (BigDecimal) obj);
                    } else if (obj instanceof Date) {
                        String date = HlpUtils.date2String((Date) obj, HlpUtils.Format_Yyyy_MM_dd);
                        this.setCell(i, date);
                    } else if (obj instanceof ByteArrayOutputStream) {
                        if (!HlpUtils.isEmpty(obj)) {
                            ByteArrayOutputStream outputStream = (ByteArrayOutputStream) obj;
                            int picIndex = this.workbook.addPicture(outputStream.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG);
                            CreationHelper helper = this.workbook.getCreationHelper();
                            ClientAnchor anchor = helper.createClientAnchor();
                            anchor.setCol1(i);
                            anchor.setRow1(this.rowIndex);
                            Picture picture = drawing.createPicture(anchor, picIndex);
                            picture.resize();
                        }
                    } else {
                        this.setCell(i, obj.toString());
                    }
                }
            }
        }
        this.rowIndex++;
    }

    public void writeRow(int fieldIndex, Object o, String dateFormat) throws Exception {
        this.row = this.sheet.createRow(this.rowIndex);
        List<String> fields = readExcelData.get(fieldIndex);
        Drawing drawing = this.sheet.createDrawingPatriarch();
        for (int i = 0; i < fields.size(); i++) {
            Method method = methodList.get(i);
            if (method != null) {
                Object obj = method.invoke(o);
                if (obj != null) {
                    if (obj instanceof String) {
                        this.setCell(i, (String) obj);
                    } else if (obj instanceof Integer) {
                        this.setCell(i, (Integer) obj);
                    } else if (obj instanceof Double) {
                        this.setCell(i, (Double) obj);
                    } else if (obj instanceof Long) {
                        this.setCell(i, (Long) obj);
                    } else if (obj instanceof BigDecimal) {
                        this.setCell(i, (BigDecimal) obj);
                    } else if (obj instanceof Date) {
                        String date = HlpUtils.date2String((Date) obj, dateFormat);
                        this.setCell(i, date);
                    } else if (obj instanceof ByteArrayOutputStream) {
                        if (!HlpUtils.isEmpty(obj)) {
                            ByteArrayOutputStream outputStream = (ByteArrayOutputStream) obj;
                            int picIndex = this.workbook.addPicture(outputStream.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG);
                            CreationHelper helper = this.workbook.getCreationHelper();
                            ClientAnchor anchor = helper.createClientAnchor();
                            anchor.setCol1(i);
                            anchor.setRow1(this.rowIndex);
                            Picture picture = drawing.createPicture(anchor, picIndex);
                            picture.resize();
                        }
                    } else {
                        this.setCell(i, obj.toString());
                    }
                }
            }
        }
        this.rowIndex++;
    }

    public enum ExcelType {
        XLS("xls"), XLSX("xlsx");

        private final String type;

        ExcelType(String type) {
            this.type = type;
        }

        public String getType() {
            return type;
        }
    }
}
